package com.liliya.dao.impl;

import com.liliya.dao.BaseDao;
import com.liliya.dao.UserDao;
import com.liliya.pojo.Role;
import com.liliya.pojo.User;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description TODO
 * @Author hhy
 * @Date 2021/1/5 15:18
 */
public class UserDaoImpl extends BaseDao implements UserDao {
    @Override
    public User findUserByName(String name) {
        User user = null;
        String sql = "select \"username\",password,num,name from T_USER u,T_ROLE r  where \"username\" = ? and u.ROLENUM = r.NUM";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, name);
            rs = pstm.executeQuery();
            while (rs.next()) {
                user = new User();
                user.setUsername(rs.getString("USERNAME"));
                user.setPassword(rs.getString("PASSWORD"));
                user.setRole(new Role(rs.getInt("num"), rs.getString("name")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return user;
    }

    @Override
    public void addUser(User user) {
        String sql = "insert into T_USER values (?,?,?)";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1, user.getUsername());
            pstm.setString(2, user.getPassword());
            pstm.setInt(3, user.getRole().getNum());
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(sql);
        close();
    }

    @Override
    public List<User> findUsers(Integer start, Integer end) {
        List<User> users = new ArrayList<>();
        String sql = "select \"username\",password,rolenum,name from (select \"username\",password,rolenum,name,rownum r from T_USER u,T_ROLE r  where u.ROLENUM = r.NUM ) e where e.r between ? and ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, start);
            pstm.setInt(2, end);
            rs = pstm.executeQuery();
            while (rs.next()) {
                users.add(new User(rs.getString("username"), rs.getString("password"), new Role(rs.getInt("rolenum"), rs.getString("name"))));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return users;
    }

    @Override
    public Integer findUsersCount() {
        String sql = "select count(1) c from T_USER";
        Integer count = 0;
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()){
                count = rs.getInt("c");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
        return count;
    }

    @Override
    public void deleteUser(String username) {
        String sql = "delete T_USER where \"username\" = ?";
        try {
            conn = getConn();
            pstm = conn.prepareStatement(sql);
            pstm.setString(1,username);
            pstm.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        close();
        System.out.println(sql);
    }

    @Override
    public boolean isReg(String username) {
        User user = findUserByName(username);
        return user==null?true:false;
    }
}
